﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace TMS
{
    class Offers
    {
       public Decimal Price;
       public Decimal VAT;
       public Decimal PriceWithVAT;
       public Decimal VATProc;
       public int Projcet_ID;
       public int ID;
       public int OfferNo;
       public string Date;
       public string Status;
       public string ProjectName;
       public string PartnerName;
       public string Description;
       public string FinishDate;
       public string ValidDate;
       public string Payment;
       public string Guarantee;
       public string Request;
       public string Responsible;
       public string Note;


        public Offers()
        {
        }

        public void AddOfferInDB()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();


            string sqlInsertProduct = "INSERT INTO offers VALUES (DEFAULT,'" +
                                        this.Projcet_ID + "','" +
                                        this.Date + "'," +
                                        "@Price,'" +
                                        this.Status + "'," +
                                        "@VAT," +
                                        "@VATProc," +
                                        "@PriceWithVAT,'" +
                                        this.OfferNo + "','"+
                                        this.ProjectName + "','" +
                                        this.PartnerName + "','" +
                                        this.Description + "','" +
                                        this.FinishDate + "','" +
                                        this.ValidDate + "','" +
                                        this.Payment + "','" +
                                        this.Guarantee + "','" +
                                        this.Request + "','" +
                                        this.Responsible + "','" +
                                        this.Note+"')";

            MySqlCommand cmd = new MySqlCommand(sqlInsertProduct, mysqlCon);
            cmd.Parameters.Add("@Price", MySqlDbType.Double, 50).Value = this.Price;
            cmd.Parameters.Add("@VAT", MySqlDbType.Double, 50).Value = this.VAT;
            cmd.Parameters.Add("@VATProc", MySqlDbType.Double, 50).Value = this.VATProc;
            cmd.Parameters.Add("@PriceWithVAT", MySqlDbType.Double, 50).Value = this.PriceWithVAT;
            cmd.ExecuteNonQuery();



            mysqlCon.Close();
        }

        public DataSet GetOffersForProject(int Project_ID)
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT * from Offers WHERE Project_ID="+Project_ID;
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table;
        }

        public Int32 GetLastOffer()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            string sqlSelectAll = "SELECT MAX(Number) FROM Offers";

            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();
            try
            {
                return Convert.ToInt32(result.Tables[0].Rows[0].ItemArray[0].ToString());
            }
            catch
            {
                return 0;
            }

        }
            public string GetPartner(int OfferNo)
            {
                MySqlDataAdapter MyDA = new MySqlDataAdapter();
                string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

                MySqlConnection mysqlCon = new MySqlConnection(connString);

                mysqlCon.Open();

                string sqlSelectAll = "SELECT PartnerName FROM Offers WHERE Number="+OfferNo;

                MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
                DataSet result = new DataSet();
                MyDA.Fill(result);
                return result.Tables[0].Rows[0].ItemArray[0].ToString();
            }

            public void DeleteOffer()
            {
                DataSet table = new DataSet();
                MySqlDataAdapter MyDA = new MySqlDataAdapter();
                string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
                MySqlConnection mysqlCon = new MySqlConnection(connString);
                mysqlCon.Open();
                string sql = "DELETE FROM offered_products WHERE OfferID='" + this.OfferNo + "'";
                MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
                cmd.ExecuteNonQuery();
                sql = "DELETE FROM offers WHERE ID='" + this.ID + "'";
                cmd = new MySqlCommand(sql, mysqlCon);
                cmd.ExecuteNonQuery();
                mysqlCon.Close();
            }

            public void CloseOffer(string OfferID, string Status)
            {
                string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

                MySqlConnection mysqlCon = new MySqlConnection(connString);

                mysqlCon.Open();
                string sqlUpdateProduct = "UPDATE offers SET Status ='"+Status+"' WHERE ID = '" + OfferID + "'";
                MySqlCommand cmd = new MySqlCommand(sqlUpdateProduct, mysqlCon);
                cmd.ExecuteNonQuery();
                mysqlCon.Close();
            }
    }
}
